import requests
from lxml import etree
import json

headers = {
            'user-agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3947.100 Safari/537.36',
        }

# 获取打折、原价、秒杀价
def get_price(p_id):
    url = "https://item-soa.jd.com/getWareBusiness?skuId="+str(p_id)+"&cat=9987,830,13658&area=19_1655_4886_0&shopId=1000096602&venderId=1000096602&paramJson={%22platform2%22:%22100000000001%22,%22specialAttrStr%22:%22p0ppp1p1ppppp2ppppppppppp%22,%22skuMarkStr%22:%2200%22}&num=1"
    res = requests.get(url,headers=headers).json()
    # 打折
    discount = res['price']['discount']
    # 原价
    op = res['price']['op']
    # 秒杀价
    p = res['price']['p']

    return discount,op,p

# 获取评论数、好评数、中评数、差评数、好评率
def CommentCount(product_id):
    url = "https://club.jd.com/comment/productCommentSummaries.action?referenceIds="+str(product_id)+"&_=1615290596097"
    res = requests.get(url, headers=headers).json()
    Comment_Count = (res['CommentsCount'][0]['CommentCountStr']).replace("+","")
    ####对"万"进行处理
    if "万" in Comment_Count:
        Comment_Count = Comment_Count.replace("万","")
        Comment_Count = str(int(Comment_Count)*10000)

    GoodCountStr = res['CommentsCount'][0]['GoodCountStr']
    GeneralCountStr = res['CommentsCount'][0]['GeneralCountStr']
    PoorCountStr = res['CommentsCount'][0]['PoorCountStr']

    GoodRate = res['CommentsCount'][0]['GoodRate']

    return Comment_Count,GoodCountStr,GeneralCountStr,PoorCountStr,GoodRate

##写入到excel
import openpyxl
outwb = openpyxl.Workbook()  # 打开一个将写的文件
outws = outwb.create_sheet(index=0)  # 在将写的文件创建sheet

outws.cell(row=1, column=1, value="index")
outws.cell(row=1, column=2, value="商品名称")
outws.cell(row=1, column=3, value="打折")
outws.cell(row=1, column=4, value="原价")
outws.cell(row=1, column=5, value="秒杀价")
outws.cell(row=1, column=6, value="评论数")
outws.cell(row=1, column=7, value="好评数")
outws.cell(row=1, column=8, value="中评数")
outws.cell(row=1, column=9, value="差评数")
outws.cell(row=1, column=10, value="好评率")
count = 2
#打折、原价、秒杀价,评论数、好评数、中评数、差评数、好评率

def get_data1():
    global count
    url = "https://prodev.jd.com/mall/active/3ThPi2MDs7DemJbFtR6baBuJpXJC/index.html?babelChannel=ttt5"
    res = requests.get(url,headers=headers)
    res.encoding = 'utf-8'
    text = res.text
    selector = etree.HTML(text)
    list = selector.xpath('//*[@class="basement_body oneline2"]/a')
    for i in list:
        title = i.xpath('.//div[@class="element_item_text"]/span/text()')[0]
        href = i.xpath('.//@href')[0]
        p_id = href.split("/")[-1].split(".")[0]
        t1 = get_price(p_id)
        t2 = CommentCount(p_id)
        outws.cell(row=count, column=1, value=str(count - 1))
        outws.cell(row=count, column=2, value=title)
        outws.cell(row=count, column=3, value=t1[0])
        outws.cell(row=count, column=4, value=t1[1])
        outws.cell(row=count, column=5, value=t1[2])
        outws.cell(row=count, column=6, value=t2[0])
        outws.cell(row=count, column=7, value=t2[1].replace("+", ""))
        outws.cell(row=count, column=8, value=t2[2].replace("+", ""))
        outws.cell(row=count, column=9, value=t2[3].replace("+", ""))
        outws.cell(row=count, column=10, value=t2[4])
        count = count+1

def get_data2():
    global count
    headers = {
        'content-type':'application/x-www-form-urlencoded; charset=UTF-8',
        'origin':'https://prodev.jd.com',
        'sec-fetch-mode':'cors',
        'sec-fetch-site':'same-site',
        'user-agent':'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3947.100 Safari/537.36',

    }
    url = "https://api.m.jd.com/client.action?functionId=qryJediPcBabelFloors&body=%7B%22activityId%22%3A%223ThPi2MDs7DemJbFtR6baBuJpXJC%22%2C%22pageNum%22%3A%22-1%22%2C%22innerAnchor%22%3A%22%22%2C%22focus%22%3A%22%22%2C%22forceTop%22%3A%22%22%2C%22innerExtId%22%3A%22%22%2C%22hideTopFoot%22%3A%22%22%2C%22innerLink%22%3A%22%22%2C%22mitemAddrId%22%3A%22%22%2C%22geo%22%3A%7B%22lng%22%3A%22%22%2C%22lat%22%3A%22%22%7D%2C%22flt%22%3A%22%22%2C%22jda%22%3A%22122270672.16029259591921351541594.1602925959.1623936717.1623979102.25%22%2C%22pageId%22%3A%22%22%2C%22mtm_source%22%3A%22%22%2C%22mtm_subsource%22%3A%22%22%2C%22USER_FLAG_CHECK%22%3A%22%22%2C%22sid%22%3A%22%22%2C%22paginationParam%22%3A%222%22%2C%22paginationFlrs%22%3A%22%5B%5B107230258%2C107230259%2C107230260%2C107230261%2C107230262%2C107230263%2C107230264%2C107230265%2C107230266%2C107230267%2C107230269%5D%2C%5B107230270%2C107230271%2C107241285%2C107241286%2C107230272%2C107230273%2C107230274%2C107230275%2C107230276%2C107230277%2C107230278%2C107230279%2C107230280%2C107230281%2C107230282%2C107230283%2C107230284%2C107230285%2C107230286%2C107230287%2C107230289%2C107230293%2C107230294%2C107230295%2C107230296%2C107230299%2C107230297%2C107230298%2C107230300%2C107230301%2C107230302%2C107230303%2C107230304%2C107230305%5D%2C%5B107230306%2C107230307%2C107230308%2C107230309%2C107230310%5D%5D%22%2C%22transParam%22%3A%22%7B%5C%22bsessionId%5C%22%3A%5C%22eab2ee47-9143-4c7c-8886-0b04934fe018%5C%22%2C%5C%22babelChannel%5C%22%3A%5C%22%5C%22%2C%5C%22actId%5C%22%3A%5C%2200943001%5C%22%2C%5C%22enActId%5C%22%3A%5C%223ThPi2MDs7DemJbFtR6baBuJpXJC%5C%22%2C%5C%22pageId%5C%22%3A%5C%222744278%5C%22%2C%5C%22encryptCouponFlag%5C%22%3A%5C%221%5C%22%7D%22%2C%22closePagination%22%3A%220%22%7D&screen=750*1334&client=babelnode&clientVersion=1.0.0&sid=&uuid=&area=&loginType=3"
    res = requests.post(url,headers=headers)
    data = res.json()
    floorList = data['floorList']
    for i in floorList:
        try:
            if len(i['waresList'])>0:
                groupInfoList = i['waresList'][0]['groupInfoList']
                for j in groupInfoList:
                    title = j["name"]
                    t1 = get_price(j["skuId"])
                    t2 = CommentCount(j["skuId"])
                    outws.cell(row=count, column=1, value=str(count - 1))
                    outws.cell(row=count, column=2, value=title)
                    outws.cell(row=count, column=3, value=t1[0])
                    outws.cell(row=count, column=4, value=t1[1])
                    outws.cell(row=count, column=5, value=t1[2])
                    outws.cell(row=count, column=6, value=t2[0])
                    outws.cell(row=count, column=7, value=t2[1].replace("+", ""))
                    outws.cell(row=count, column=8, value=t2[2].replace("+", ""))
                    outws.cell(row=count, column=9, value=t2[3].replace("+", ""))
                    outws.cell(row=count, column=10, value=t2[4])
                    count = count + 1
        except:
            pass


get_data1()
get_data2()

outwb.save("618畅销京东商品-李运辰.xls")  # 保存结果
